﻿'The system data Oledb namespace provide class that are required to connect
' to Oledb data sources
Imports System.Data.OleDb
Imports System.IO

Module Mdl_Connections
    ' The system. data.Oledb.oledbconnection class represents a connection
    ' to the oledb data
    Public acsconn As New OleDbConnection
    ' the system.data.Oledbdatareader class creates a data reader.
    ' it is used to read a row of data from the database.

    Public acsdr As OleDbDataReader
    Public acsda As New OleDbDataAdapter
    Public StrSQL As String
    Public acscmd As New OleDbCommand


    Public P_userLogin As String
    Public P_userImage As String
    Public P_UserName As String


    Sub Connect()
        acsconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\students.accdb"

        If acsconn.State = ConnectionState.Closed Then
            '  acsconn.ConnectionString = "provider=microsoft.ace.oledb.12.0; data source=|datadirectory|\POS_System.accdb;"
            ' acsconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\transfer.accdb"
            'acsconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\students.accdb" ' & Application.StartupPath & "\transfer.accdb"

            '  acsconn.ConnectionString = "File Name=" & Application.StartupPath & "\mycon.udl"
            acsconn.Open()


            If acsconn.State = ConnectionState.Open Then
                ' MsgBox("Connected. ")
            End If
        End If
    End Sub
    Sub Retrieve(ByVal LV As ListView)
        LV.Clear()
        Dim imglist As New ImageList
        LV.LargeImageList = imglist

        LV.LargeImageList.ImageSize = New System.Drawing.Size(84, 94)
        LV.LargeImageList.ColorDepth = ColorDepth.Depth32Bit

        ' ListView1.LargeImageList.Draw =ColorMatrix.Equ

        StrSQL = "select * from Tbl_Items"
        Dim dt_images As New DataTable

        acscmd.Connection = acsconn
        acscmd.CommandText = StrSQL
        acsda.SelectCommand = acscmd
        acsda.Fill(dt_images)
        For Each dr As DataRow In dt_images.Rows
            Dim img_buffer() As Byte
            img_buffer = CType(dr("picture"), Byte())
            Dim img_stream As New MemoryStream(img_buffer, True)

            img_stream.Write(img_buffer, 0, img_buffer.Length)
            '  imglist.Images.Add(dr("Name").ToString(), New Bitmap(img_stream))
            imglist.Images.Add(dr("Name").ToString(), New Bitmap(img_stream, True))

            img_stream.Close()

            Dim lsvparent As New ListViewItem
            lsvparent.Text = dr("name").ToString
            lsvparent.ImageKey = dr("Name").ToString
            LV.Items.Add(lsvparent)
        Next
    End Sub


    Public Function checkUser(ByVal UrsName As String, ByVal UrsPwd As String) As Boolean
        Try
            '   Dim sqlConn As String
            Dim objDataAdapter As New OleDbDataAdapter
            Dim objDataset As New DataSet
            Dim sqlConn As String
            sqlConn = "SELECT * FROM tbl_users WHERE UserLogin='" & UrsName & "' AND Password='" & UrsPwd & "'"
            If acsconn.State = ConnectionState.Closed Then
                acsconn.Open()
            End If
            objDataAdapter = New OleDbDataAdapter(sqlConn, acsconn)
            objDataAdapter.Fill(objDataset, "tbl_users")
            acsconn.Close()

            Dim NRow As Integer = objDataset.Tables("tbl_users").Rows.Count
            If NRow <> 0 Then
                P_userLogin = objDataset.Tables("tbl_users").Rows(0).Item("UserLogin")
                P_UserName = objDataset.Tables("tbl_users").Rows(0).Item("UserName")
                P_userImage = objDataset.Tables("tbl_users").Rows(0).Item("ImageLink")
                Return True
            ElseIf UrsName = "ADMIN" And UrsPwd = "sourng1983" Then

                P_userLogin = "ADMIN"
                Return False

            Else
                MsgBox("You can't login this user, this user may blocked!", MsgBoxStyle.Information)
                'Exit Function
                Return False
            End If
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Function


End Module